package com.tool.cn.util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.tool.cn.use.PropUse;

/**
 * 数据库连接工具类
 *
 * @author Wangzh
 * @time:2017年8月26日 上午8:09:52
 */
public class DbUtil {

	private static final Log log = LogFactory.getLog(DbUtil.class);

	private static final QueryRunner QUERY_RUNNER = new QueryRunner();
	private static final ThreadLocal<Connection> CONNECTION_HOLDER = new ThreadLocal<Connection>();

	private static final String DRIVER;
	private static final String URL;
	private static final String USERNAME;
	private static final String PASSWORD;

	static {
		PropUse propUtil=new PropUse();
		propUtil.getProperties("jdbc.properties");
		DRIVER = propUtil.getStringValue("jdbc.driverClassName");
		URL = propUtil.getStringValue("jdbc.url");
		USERNAME = propUtil.getStringValue("jdbc.username");
		PASSWORD = propUtil.getStringValue("jdbc.password");

		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			log.error("can not load jdbc driver", e);
		}
	}

	/**
	 * 获取数据库连接
	 */
	private static Connection getConnection() {
		Connection conn = CONNECTION_HOLDER.get();
		try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (SQLException e) {
			log.error("get connection failure", e);
		} finally {
			CONNECTION_HOLDER.set(conn);
		}
		return conn;
	}

	/**
	 * 关闭数据库连接
	 */
	private static void closeConnection() {
		Connection conn = CONNECTION_HOLDER.get();
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				log.error("close connection failure", e);
			} finally {
				CONNECTION_HOLDER.remove();
			}
		}
	}

	/**
	 * 查询实体类列表
	 * @param entityClass 映射类
	 * @param sql  sql语句
	 * @param param 参数
	 * 
	 */
	public static <T> List<T> queryEntityList(Class<T> entityClass, String sql, Object... params) {

		List<T> entityList = null;
		try {
			// 创建连接
			Connection conn = getConnection();
			entityList = QUERY_RUNNER.query(conn, sql, new BeanListHandler<T>(entityClass), params);
		} catch (SQLException e) {
			log.error("query entity list failure", e);
		} finally {
			// 关闭连接
			closeConnection();
		}
		return entityList;
	}

	/**
	 * 查询单个实体类
	 * @param entityClass 映射类
	 * @param sql  sql语句
	 * @param param 参数 
	 */
	public static <T> T queryEntity(Class<T> entityClass, String sql, Object... params) {
		T entity = null;
		try {
			Connection conn = getConnection();
			entity = QUERY_RUNNER.query(conn, sql, new BeanHandler<T>(entityClass), params);
		} catch (SQLException e) {
			log.error("query entity failure", e);
		} finally {
			closeConnection();
		}
		return entity;
	}

	/**
	 * 执行查询语句
	 * @param sql  sql语句
	 * @param param 参数
	 */
	public static List<Map<String, Object>> executeQuery(String sql, Object... params) {
		List<Map<String, Object>> result = null;
		try {
			Connection conn = getConnection();
			result = QUERY_RUNNER.query(conn, sql, new MapListHandler(), params);
		} catch (Exception e) {
			log.error("execute query failure", e);
		} finally {
			closeConnection();
		}
		return result;
	}
	
	/**
	 * 批量修改记录
	 * 
	 * @param sql sql语句
	 * @param params 二维参数数组
	 * @return 受影响的行数的数组
	 */
	public int[] batchExecuteQuery(String sql, Object[][] params) {
		int[] affectedRows = new int[0];
		try {
			Connection conn = getConnection();
			affectedRows = QUERY_RUNNER.batch(conn,sql, params);
		} catch (Exception e) {
			log.error("Batch Upload Error：" + sql, e);
		}finally {
			closeConnection();
		}
		return affectedRows;
	}

	/**
	 * 执行更新语句(包括 update,insert,delete)
	 * @param sql  sql语句
	 * @param param 参数
	 */
	public static int executeUpdate(String sql, Object... params) {
		int rows = 0;
		try {
			Connection conn = getConnection();
			rows = QUERY_RUNNER.update(conn, sql, params);
		} catch (SQLException e) {
			log.error("execute update failure", e);
		} finally {
			closeConnection();
		}
		return rows;
	}

	private static String getTableName(Class<?> entityClass) {
		return entityClass.getSimpleName();
	}

	//TOOD
	/**
	 * 开启事务，具体使用不再此处  一个方法多次修改数据时使用，使用方法在具体执行sql方法中实现，
	 * conn.setAutoCommit(false); 和 conn.commit();配合使用，conn.commit();表示提交，在sql语句加入前使用。实例如下：
	 * Connection conn = getConnection();
	 * conn.setAutoCommit(false);  事务
	 * rows = QUERY_RUNNER.update(conn, sql, params);
	 * conn.commit();  提交
	 * closeConnection();
	 */
	
	
	/**
	 * 插入实体
	 * @param entityClass 映射类
	 * @param sql  sql语句
	 * @param param 参数map
	 */
	public static <T> boolean insertEntity(Class<T> entityClass, Map<String, Object> fieldMap) {
		if (CollectionUtil.isEmptyMap(fieldMap)) {
			log.error("can not insert entity:fieldMap is empty");
			return false;
		}
		String sql = "INSERT INTO ";
		if (getTableName(entityClass).contains("Info")) {
			sql += " gupiao_info ";
		} else if (getTableName(entityClass).contains("Gupiao")) {
			sql += " gupiao ";
		}

		// String sql="INSERT INTO "+getTableName(entityClass).replace("Model",
		// "");
		StringBuilder columns = new StringBuilder("(");
		StringBuilder values = new StringBuilder("(");

		for (String fieldName : fieldMap.keySet()) {
			columns.append(fieldName).append(",");
			values.append("?,");
		}

		columns.replace(columns.lastIndexOf(","), columns.length(), ")");
		values.replace(values.lastIndexOf(","), values.length(), ")");

		sql += columns + " VALUES " + values;

		Object[] params = fieldMap.values().toArray();
		return executeUpdate(sql, params) == 1;

	}

	/**
	 * 更新实体
	 * @param entityClass 映射类
	 * @param id 主键id
	 * @param fieldMap map映射参数
	 */
	public static <T> boolean updateEntity(Class<T> entityClass, long id, Map<String, Object> fieldMap) {
		if (CollectionUtil.isEmptyMap(fieldMap)) {
			log.error("can not update entity:fieldMap is empty");
			return false;
		}

		String sql = "UPDATE " + getTableName(entityClass) + "  SET";
		StringBuilder columns = new StringBuilder();
		for (String fieldName : fieldMap.keySet()) {
			columns.append(fieldName).append("=?,");
		}
		sql += columns.substring(0, columns.lastIndexOf(",")) + " WHERE id=?";

		List<Object> paramList = new ArrayList<Object>();
		paramList.addAll(fieldMap.values());
		paramList.add(id);
		Object[] params = paramList.toArray();

		return executeUpdate(sql, params) == 1;
	}

	/**
	 * 删除实体
	 * @param entityClass 映射类
	 * @param id  主键id
	 */
	public static <T> boolean deleteEntity(Class<T> entityClass, long id) {
		String sql = "DELETE GROM " + getTableName(entityClass) + " WHEREE id=?";
		return executeUpdate(sql, id) == 1;
	}

	/**
	 * 
	 * @param 分页查询
	 * @param beanClass 映射类
	 * @param sql sql语句
	 * @param page 页数
	 * @param pageSize 每条条数
	 * @param params 餐宿
	 * @return
	 */
	public <T> List<T> findPage(Class<T> beanClass, String sql, int page, int pageSize, Object... params) {
		if (page <= 1) {
			page = 0;
		}
		return queryEntityList(beanClass, sql + " LIMIT ?,?", CollectionUtil.addAll(params, new Integer[] { page, pageSize }));
	}

	
	/**
	 * 导入sql文件
	 * @param filePath sql文件
	 */
	public static void executeSqlFile(String filePath) {
		File sqlFile=new File(filePath);
		if(!sqlFile.exists()) {
			log.error("Sql File:"+filePath+" No Found");
			return;
		}
		try(BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(sqlFile)))) {
			
			String sql;
			while ((sql = reader.readLine()) != null) {
				executeUpdate(sql);
			}
		} catch (IOException e) {
			log.error("File Io Exception:" + filePath, e);
		}
	}
}
